<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    
<meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1"/>


<meta http-equiv="Cache-Control" content="no-transform" />
<meta http-equiv="Cache-Control" content="no-siteapp" />

<meta name="theme-color" content="#f8f5ec" />
<meta name="msapplication-navbutton-color" content="#f8f5ec">
<meta name="apple-mobile-web-app-capable" content="yes">
<meta name="apple-mobile-web-app-status-bar-style" content="#f8f5ec">



  <meta name="description" content="SQLAIchemy数据模型关联"/>




  <meta name="keywords" content="开发, flask, SQLAlchemy, 八一" />



  <meta name="baidu-site-verification" content="HhUstaSjr0" />



  <meta name="google-site-verification" content="UA-102975942-1" />






  <link rel="alternate" href="/atom.xml" title="八一">




  <link rel="shortcut icon" type="image/x-icon" href="/favicon.ico?v=2.6.0" />



<link rel="canonical" href="https://bay1.top/2017/08/09/SQLAIchemy数据模型关联/"/>


<link rel="stylesheet" type="text/css" href="/css/style.css?v=2.6.0" />
<link rel="stylesheet" type="text/css" href="/css/prettify.css" media="screen" />
<link rel="stylesheet" type="text/css" href="/css/sons-of-obsidian.css" media="screen" />



  <link rel="stylesheet" type="text/css" href="/lib/fancybox/jquery.fancybox.css" />




  
  <script id="baidu_analytics">
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "https://hm.baidu.com/hm.js?9a885cc9fb6cd7bcef579deb8efe8a70";
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
  </script>



  <script id="google_analytics">
    (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
        (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
        m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
        })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

        ga('create', 'UA-102975942-1', 'auto');
        ga('send', 'pageview');
  </script>










    <title> SQLAIchemy数据模型关联 - 八一 </title>
  </head>

  <body><div id="mobile-navbar" class="mobile-navbar">
  <div class="mobile-header-logo">
    <a href="/." class="logo">八一</a>
  </div>
  <div class="mobile-navbar-icon">
    <span></span>
    <span></span>
    <span></span>
  </div>
</div>

<nav id="mobile-menu" class="mobile-menu slideout-menu">
  <ul class="mobile-menu-list">
    
      <a href="/archives">
        <li class="mobile-menu-item">
          
          
            文章
          
        </li>
      </a>
    
      <a href="/tags">
        <li class="mobile-menu-item">
          
          
            标签
          
        </li>
      </a>
    
      <a href="/about">
        <li class="mobile-menu-item">
          
          
            关于/友链
          
        </li>
      </a>
    
      <a href="/search">
        <li class="mobile-menu-item">
          
          
            站内搜索
          
        </li>
      </a>
    
  </ul>
</nav>

    <div class="container" id="mobile-panel">
      <header id="header" class="header"><div class="logo-wrapper">
  <a href="/." class="logo">八一</a>
</div>

<nav class="site-navbar">
  
    <ul id="menu" class="menu">
      
        <li class="menu-item">
          <a class="menu-item-link" href="/archives">
            
            
              文章
            
          </a>
        </li>
      
        <li class="menu-item">
          <a class="menu-item-link" href="/tags">
            
            
              标签
            
          </a>
        </li>
      
        <li class="menu-item">
          <a class="menu-item-link" href="/about">
            
            
              关于/友链
            
          </a>
        </li>
      
        <li class="menu-item">
          <a class="menu-item-link" href="/search">
            
            
              站内搜索
            
          </a>
        </li>
      
    </ul>
  
</nav>

      </header>

      <main id="main" class="main">
        <div class="content-wrapper">
          <div id="content" class="content">
            
  
  <article class="post">
    <header class="post-header">
      <h1 class="post-title">
        
          SQLAIchemy数据模型关联
        
      </h1>

      <div class="post-meta">
        <span class="post-time">
          2017-08-09
        </span>
        
        
        
      </div>
    </header>

    
    
  <div class="post-toc" id="post-toc">
    <h2 class="post-toc-title">文章目录</h2>
    <div class="post-toc-content">
      <ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#用户表示例"><span class="toc-text">用户表示例</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#图片表示例"><span class="toc-text">图片表示例</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#标签表示例"><span class="toc-text">标签表示例</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#约束表示例"><span class="toc-text">约束表示例</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#一对一"><span class="toc-text">一对一</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#一对多"><span class="toc-text">一对多</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#多对多"><span class="toc-text">多对多</span></a></li></ol>
    </div>
  </div>


    <div class="post-content">
      
        <p><strong>以下为读《深入理解Flask》</strong> <a id="more"></a></p>
<p>使用SQLAIchemy要建立相应的数据模型<br>而实际中模型之间又有一些不同的关系</p>
<blockquote>
<p>比如一一对应，一对多个，多对多个<br>模型之间建立关系便必不可少了</p>
</blockquote>
<h2 id="用户表示例"><a href="#用户表示例" class="headerlink" title="用户表示例"></a>用户表示例</h2><table>
<thead>
<tr>
<th>ID</th>
<th>Username</th>
<th>Password</th>
<th>UserAvator</th>
<th>Email</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>bayi</td>
<td>123456</td>
<td>upload/avator1jpg</td>
<td><a href="mailto:328588917@qq.com" target="_blank" rel="noopener">328588917@qq.com</a></td>
</tr>
<tr>
<td>2</td>
<td>babai</td>
<td>123654</td>
<td>upload/avator2.jpg</td>
<td><a href="mailto:123456@qq.com" target="_blank" rel="noopener">123456@qq.com</a></td>
</tr>
<tr>
<td>3</td>
<td>xiaoming</td>
<td>654321</td>
<td>upload/avator3.jpg</td>
<td><a href="mailto:123654@qq.com" target="_blank" rel="noopener">123654@qq.com</a></td>
</tr>
</tbody>
</table>
<h2 id="图片表示例"><a href="#图片表示例" class="headerlink" title="图片表示例"></a>图片表示例</h2><table>
<thead>
<tr>
<th>ID</th>
<th>user_id</th>
<th>description</th>
<th>Arddess</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>1</td>
<td>第一张图片</td>
<td>upload/1.jpg</td>
</tr>
<tr>
<td>2</td>
<td>1</td>
<td>第二张图片</td>
<td>upload/2.jpg</td>
</tr>
<tr>
<td>3</td>
<td>3</td>
<td>第三张图片</td>
<td>upload/3.jpg</td>
</tr>
</tbody>
</table>
<h2 id="标签表示例"><a href="#标签表示例" class="headerlink" title="标签表示例"></a>标签表示例</h2><table>
<thead>
<tr>
<th>ID</th>
<th>tag</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>人物</td>
</tr>
<tr>
<td>2</td>
<td>风景</td>
</tr>
<tr>
<td>3</td>
<td>动物</td>
</tr>
</tbody>
</table>
<h2 id="约束表示例"><a href="#约束表示例" class="headerlink" title="约束表示例"></a>约束表示例</h2><table>
<thead>
<tr>
<th>ID 1</th>
<th>ID 2</th>
<th>ID 3</th>
</tr>
</thead>
<tbody>
<tr>
<td>pic_id 1</td>
<td>pic_id  2</td>
<td>pic_id  1</td>
</tr>
<tr>
<td>tag_id 1</td>
<td>tag_id  1</td>
<td>tag_id  2</td>
</tr>
</tbody>
</table>
<h2 id="一对一"><a href="#一对一" class="headerlink" title="一对一"></a>一对一</h2><blockquote>
<p>这种关联似乎很少用到，夫妻似乎是很好的生活模型<br>可以在丈夫一方加上外键，并加上唯一参数</p>
</blockquote>
<figure class="highlight haskell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="class"><span class="keyword">class</span> husband(<span class="title">db</span>.<span class="type">Model</span>):</span></span><br><span class="line"><span class="class">    __tablename__ = 'husband'</span></span><br><span class="line"><span class="class">    id = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">Integer</span>, <span class="title">primary_key</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    husbandname = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">String(80)</span>, <span class="title">unique</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    wife = db.relationship('<span class="title">wife'</span>, <span class="title">backref</span>='<span class="title">husband'</span>, <span class="title">lazy</span>='<span class="title">dynamic'</span>,<span class="title">unique</span>=<span class="type">True</span>)</span></span><br></pre></td></tr></table></figure>
<blockquote>
<p>在妻子一方加上指向husband的值</p>
</blockquote>
<figure class="highlight haskell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="class"><span class="keyword">class</span> wife(<span class="title">db</span>.<span class="type">Model</span>):</span></span><br><span class="line"><span class="class">    id = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">Integer</span>, <span class="title">primary_key</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">	wifename = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">String(80)</span>, <span class="title">unique</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    husbandId = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">Integer</span>, <span class="title">db</span>.<span class="type">ForeignKey</span>('<span class="title">husband</span>.<span class="title">id'</span>))</span></span><br></pre></td></tr></table></figure>
<h2 id="一对多"><a href="#一对多" class="headerlink" title="一对多"></a>一对多</h2><blockquote>
<p>这个关系算是常用的了，这次的demo里也有这个：user-&gt;picture<br>一个用户会上传多张图片，相当于把一对一里的唯一参数删去<br>user.pictures就成了一个列表</p>
</blockquote>
<figure class="highlight haskell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="class"><span class="keyword">class</span> <span class="type">User</span>(<span class="title">db</span>.<span class="type">Model</span>):</span></span><br><span class="line"><span class="class">    __tablename__ = '<span class="type">User'</span></span></span><br><span class="line"><span class="class">    id = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">Integer</span>, <span class="title">primary_key</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    username = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">String(80)</span>, <span class="title">unique</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    password= db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">String(128)</span>)</span></span><br><span class="line"><span class="class">    avator= db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">String(35)</span>, <span class="title">unique</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    email = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">String(120)</span>, <span class="title">unique</span>=<span class="type">True</span>,<span class="title">index</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    picture = db.relationship('<span class="type">Picture</span>', <span class="title">backref</span>='<span class="title">user'</span>, <span class="title">lazy</span>='<span class="title">dynamic'</span>)</span></span><br><span class="line"><span class="class"></span></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="type">Picture</span>(<span class="title">db</span>.<span class="type">Model</span>):</span></span><br><span class="line"><span class="class">    id = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">Integer</span>, <span class="title">primary_key</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    dsepriction= db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">String(5000)</span>, <span class="title">unique</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    address= db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">String(35)</span>, <span class="title">unique</span>=<span class="type">True</span>)</span></span><br><span class="line"><span class="class">    userId = db.<span class="type">Column</span>(<span class="title">db</span>.<span class="type">Integer</span>, <span class="title">db</span>.<span class="type">ForeignKey</span>('<span class="type">User</span>.<span class="title">id'</span>))</span></span><br></pre></td></tr></table></figure>
<h2 id="多对多"><a href="#多对多" class="headerlink" title="多对多"></a>多对多</h2><blockquote>
<p>比如这次用到的图片-&gt;标签<br>多对多的建立就需要一个约束表的存在</p>
</blockquote>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">#约束表</span></span><br><span class="line">relation = db.Table(<span class="string">'relation'</span>,</span><br><span class="line">    db.Column(<span class="string">'tags_id'</span>, db.Integer, db.ForeignKey(<span class="string">'tags.id'</span>),primary_key=<span class="keyword">True</span>),</span><br><span class="line">    db.Column(<span class="string">'picture_id'</span>, db.Integer, db.ForeignKey(<span class="string">'picture.id'</span>),primary_key=<span class="keyword">True</span>)</span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Picture</span><span class="params">(db.Model)</span>:</span></span><br><span class="line">    id = db.Column(db.Integer, primary_key=<span class="keyword">True</span>)</span><br><span class="line">    dsepriction= db.Column(db.String(<span class="number">5000</span>), unique=<span class="keyword">True</span>)</span><br><span class="line">    address= db.Column(db.String(<span class="number">35</span>), unique=<span class="keyword">True</span>)</span><br><span class="line">    userId = db.Column(db.Integer, db.ForeignKey(<span class="string">'User.id'</span>))</span><br><span class="line">    tags = db.relationship(</span><br><span class="line">        <span class="string">'Tags'</span>, secondary=relation,backref=db.backref(<span class="string">'picture'</span>, lazy=<span class="string">'dynamic'</span>))</span><br><span class="line"></span><br><span class="line"><span class="class"><span class="keyword">class</span> <span class="title">Tags</span><span class="params">(db.Model)</span>:</span></span><br><span class="line">    id = db .Column(db.Integer, primary_key=<span class="keyword">True</span>)</span><br><span class="line">    tag = db.Column(db.String(<span class="number">50</span>))</span><br></pre></td></tr></table></figure>
<blockquote>
<p>多对多关系一开始写就感觉对操作方面很迷，就实际操作了一下<br>实际操作一下可以体会一下他的这种用法</p>
</blockquote>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">C:\Users\bayi\Desktop\irides (master)</span><br><span class="line">(venv) λ python manage.py shell</span><br><span class="line">&gt;&gt;&gt; pic=picture.query.filter_by(id=2).first()</span><br><span class="line">&gt;&gt;&gt; pic</span><br><span class="line">&lt;app.models.Picture object at 0x000000625116A6A0&gt;</span><br><span class="line">&gt;&gt;&gt; tag1=tags.query.first()</span><br><span class="line">&gt;&gt;&gt; tag1</span><br><span class="line">&lt;app.models.Tags object at 0x000000625116AD30&gt;</span><br><span class="line">&gt;&gt;&gt; tag2=tags.query.filter_by(id=2).first()</span><br><span class="line">&gt;&gt;&gt; tag2</span><br><span class="line">&lt;app.models.Tags object at 0x0000006251182208&gt;</span><br><span class="line">&gt;&gt;&gt; pic.tags=[tag1,tag2]</span><br><span class="line">&gt;&gt;&gt; db.session.add(pic)</span><br><span class="line">&gt;&gt;&gt; db.session.commit()</span><br></pre></td></tr></table></figure>
<p><img src="https://s1.ax1x.com/2018/01/01/pSRQ76.png" alt="多对多"></p>

      
    </div>

    
      
      



      
      
    

    
      <footer class="post-footer">
        
          <div class="post-tags">
            
              <a href="/tags/开发/">开发</a>
            
              <a href="/tags/flask/">flask</a>
            
              <a href="/tags/SQLAlchemy/">SQLAlchemy</a>
            
          </div>
        
        
        
  <nav class="post-nav">
    
      <a class="prev" href="/2017/08/19/如何实现图片上传API/">
        <i class="iconfont icon-left"></i>
        <span class="prev-text nav-default">如何实现图片上传API</span>
        <span class="prev-text nav-mobile">上一篇</span>
      </a>
    
    
      <a class="next" href="/2017/08/07/SQLAIchemy对数据基本操作/">
        <span class="next-text nav-default">SQLAlchemy对数据基本操作</span>
        <span class="prev-text nav-mobile">下一篇</span>
        <i class="iconfont icon-right"></i>
      </a>
    
  </nav>

      </footer>
    

  </article>


          </div>
          
  <div class="comments" id="comments">
      <div id="disqus_thread">
        <noscript>
          Please enable JavaScript to view the
          <a href="//disqus.com/?ref_noscript">comments powered by Disqus.</a>
        </noscript>
      </div> 
    </div>
  </div>


        </div>
      </main>

      <footer id="footer" class="footer">

  <div class="social-links">
    
      
        
          <a href="https://github.com/bay1" class="iconfont icon-github" title="github"></a>
        
      
    
      
        
          <a href="http://weibo.com/3190704711/profile?topnav=1&wvr=6&is_all=1" class="iconfont icon-weibo" title="weibo"></a>
        
      
    
      
    
      
    
      
    
    
    
  </div>


<div class="copyright">
  <span class="copyright-year">
    
    &copy; 
     
      2016 - 
    
    2018
    <span class="author">bay1</span>
  </span>
</div>
      </footer>

      <div class="back-to-top" id="back-to-top">
        <i class="iconfont icon-up"></i>
      </div>
    </div>

    
  
  <script type="text/javascript">
    var disqus_config = function () {
        this.page.url = 'https://bay1.top/2017/08/09/SQLAIchemy数据模型关联/';
        this.page.identifier = '2017/08/09/SQLAIchemy数据模型关联/';
        this.page.title = 'SQLAIchemy数据模型关联';
    };
    (function() {
    var d = document, s = d.createElement('script');

    s.src = '//https-blog-flywinky-top-1.disqus.com/embed.js';

    s.setAttribute('data-timestamp', +new Date());
    (d.head || d.body).appendChild(s);
    })();  
  </script>



    
  





  
    <script type="text/javascript" src="/lib/jquery/jquery-3.1.1.min.js"></script>
  

  
    <script type="text/javascript" src="/lib/slideout/slideout.js"></script>
  

  
    <script type="text/javascript" src="/lib/fancybox/jquery.fancybox.pack.js"></script>
  


    <script type="text/javascript" src="/js/src/even.js?v=2.6.0"></script>
<script type="text/javascript" src="/js/src/bootstrap.js?v=2.6.0"></script>
<script src="/js/prettify.js"></script>
<script type="text/javascript">
$(document).ready(function(){
 $('pre').addClass('prettyprint');
   prettyPrint();
 })
</script>
  </body>
</html>
